from apps.zhcxkj_center_listing_comprehensive.test.views.t_allegro_listing import T_allegro_listing
from apps.zhcxkj_center_strategy_comprehensive.test.views.t_listing_upload_queue_allegro import \
    T_listing_upload_queue_allegro
from apps.zhcxkj_center_strategy_comprehensive.test.views.t_publish_detail_allegro import T_publish_detail_allegro
from common.setting import ensure_path_sep
from third_services.allegro.RebatyesAndPromotions.offerdetails import OfferDetails
from utils.models import AllegroEan
from utils.mysqlUtils.db_pool import Database
from utils.mysqlUtils.mongodb_utils import MongoDBLink
from utils.otherutils.MyThreadPool import MyThreadPool
from utils.otherutils.batch_processor import BatchProcessor
from utils.rabbitmq.rabbitmq_utils1 import RabbitMq_
from task import insert_data, insert_data_ean
from urllib.parse import quote_plus
paa = quote_plus("Zhcx?2021")

class GeneratePublishRepeatData:

    def generate_company_list(self):
        """
        需求：
        4、同一个渠道，一个EAN最多有1个listing（如果没有EAN，就用SKU判断）。
        5、不同渠道，如果渠道对应的公司相同，则最多允许同时有两个EAN相同的在线listing（如果没有EAN，就用SKU判断）。
        6、本次需求去掉草稿功能
        7、检测重复listing，要检测自动刊登队列/商品上传队列 等待中、处理中 数据+已在线的listing
        """
        t_allegro_source_manage_modb = MongoDBLink(
            uri='mongodb://reader:MubmnYb1caAmZjOJ#@116.205.136.83:8635,139.9.210.226:8635/?authSource=admin',
            dbname='zhcxkj_java_center', coll_name='t_allegro_source_manage')
        mongodb_filter = {}

        company_source_id_list = {}
        company_source_id = t_allegro_source_manage_modb.select(mongodb_filter, {"company": 1, "source_id": 1})
        for data in company_source_id:
            if not data.get('company'):
                continue
            if data['company'] not in company_source_id_list:
                company_source_id_list[data['company']] = []
                company_source_id_list[data['company']].append(data['source_id'])

            else:
                company_source_id_list[data['company']].append(data['source_id'])
        task_list = []
        for company, value in company_source_id_list.items():
            if len(value) > 1:
                task_list.append((company, set(value)))
        return task_list

    def generate_company_repeat_ean_data(self):
        task_list = self.generate_company_list()
        MyThreadPool(10).multithreaded(self.handler_data_by_company_, task_list)

    def generate_company_repeat_sku_data(self):
        task_list = self.generate_company_list()
        MyThreadPool(10).multithreaded(self.handler_data_by_company_sku, task_list)

    def handler_data_by_company(self, company, source_id_set):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        zhcxkj_center_order_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-JavaCommon")
        get_data = f"""
        SELECT bb.source_id,bb.sku,bb.offer_id,bb.source_sku,bb.ean,SUM(bb.sales_num)  FROM  (
        SELECT tt.*,b.sales_num FROM 
         ( 
        SELECT t.source_id,t.sku,t.offer_id,t.source_sku,t.source_name,t.ean FROM t_allegro_listing_temp

         t WHERE t.source_id in %s and   t.ean in (SELECT ean FROM `t_allegro_listing_temp`   f 
        WHERE f.source_id in %s GROUP BY f.ean HAVING(count(DISTINCT f.source_id)) >2) ) tt
        LEFT JOIN rb_report_order_source_sku_year b on tt.source_id=b.order_source_id AND tt.source_sku=b.order_source_sku
        ) bb 
        GROUP BY bb.source_id,bb.sku,bb.offer_id,bb.source_sku
        ORDER BY bb.ean 
        """
        res = zhcxkj_center_order_db.fetch_all_tuple(get_data, (set(source_id_set), set(source_id_set)))
        insert_data = list(res)
        insert_data = list(map(lambda x: (x[0], x[1], x[2], x[3], x[4], x[5], company), insert_data))
        insert_sql = """
                REPLACE INTO monitor.t_allegro_listing10 
                (source_id,sku,offer_id,source_sku,ean,sales,company)
                VALUES (%s,%s,%s,%s,%s,%s,%s);
                """
        rows = db_java_test.execute_many(insert_sql, insert_data)
        print(f"{company} 插入了 {rows}")

    def handler_data_by_company_(self, company, source_id_set):
        zhcxkj_bi_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "zhcxkj_starocks")
        get_data = f"""
        SELECT ean 
        FROM zhcxkj_center.t_allegro_listing 
        WHERE source_id in %s
        and  `status`=3 
        AND (ean is not null or ean <> "")
        and (sku is null or sku ="") 
        GROUP BY ean HAVING  count(DISTINCT source_id ) >2 
        """
        res = zhcxkj_bi_db.fetch_all_tuple(get_data, (source_id_set,))
        if not len(res):
            return
        table_list = set()
        for source_id in source_id_set:
            table_list.add(f"t_allegro_listing_{source_id % 8 + 1}")
        ean_list = list(map(lambda x: x[0], res))
        batch_processor = BatchProcessor.batch_processor(ean_list, 100)
        for batch in batch_processor:
            for table in table_list:
                insert_data_ean.delay(list(source_id_set), batch, company, table)

    def handler_data_by_company_sku(self, company, source_id_set):
        zhcxkj_bi_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "zhcxkj_starocks")
        get_data = f"""
        SELECT sku 
        FROM zhcxkj_center.t_allegro_listing 
        WHERE source_id in %s
        and  `status`=3 
        AND (ean is null or ean = "")
        and (sku is not null or sku != "") 
        GROUP BY sku HAVING  count(DISTINCT source_id ) >2 
        """
        res = zhcxkj_bi_db.fetch_all_tuple(get_data, (source_id_set,))
        if not len(res):
            return
        table_list = set()
        for source_id in source_id_set:
            table_list.add(f"t_allegro_listing_{source_id % 8 + 1}")
        sku_list = list(map(lambda x: x[0], res))
        batch_processor = BatchProcessor.batch_processor(sku_list, 100)
        for batch in batch_processor:
            for table in table_list:
                insert_data.delay(list(source_id_set), batch, company, table)

    def generate_source_id_ean(self):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        zhcxkj_starocks_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "zhcxkj_starocks")
        get_source_id_ean = f"""
        SELECT source_id,ean 
        FROM t_allegro_listing 
        WHERE `status`=3 
        AND (ean is not NULL or ean != "") 
        and (sku is null or sku = "") 
        GROUP  BY source_id,ean 
        HAVING count(*)>1
        """
        source_id_ean = zhcxkj_starocks_db.fetch_all_tuple(get_source_id_ean, ())
        if not len(source_id_ean):
            return
        print(source_id_ean)
        insert_data = list(map(lambda x: (x[0], x[1]), source_id_ean))

    def generate_source_id_sku(self):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        zhcxkj_starocks_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "zhcxkj_starocks")
        get_source_id_ean = f"""
        SELECT source_id,sku 
        FROM t_allegro_listing 
        WHERE `status`=3 
        and (sku is not null or sku !="") 
--         AND (ean is  NULL OR ean = "") 
        GROUP  BY source_id,sku 
        HAVING count(*)>1
        """
        source_id_sku = zhcxkj_starocks_db.fetch_all_tuple(get_source_id_ean, ())
        if not len(source_id_sku):
            return
        insert_data = list(map(lambda x: (x[0], x[1]), source_id_sku))
        insert_sql = """
                insert INTO monitor.t_allegro_listing_source_id_sku_repeat
                (source_id,sku,create_time)
                VALUES (%s,%s,now());
                """
        rows = db_java_test.execute_many(insert_sql, insert_data)
        # mp = RabbitMq_(exchange_name="", queue_name=AllegroEan.ALLEGRO_LISTING_SOURCE_ID_SKU_REPEAT.value,
        #                routing_key=AllegroEan.ALLEGRO_LISTING_SOURCE_ID_SKU_REPEAT.value)
        # for mq_message in source_id_ean:
        #     # 发送到MQ中 (source_id,sku)
        #     mp.send_message(message=mq_message)

    def main(self):
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")

        get_sql = """
        select source_id,ean 
        from monitor.t_allegro_listing_unpublish
        where ean != ""
        """
        res = db_java_test.fetch_all_tuple(get_sql, ())
        print(res)
        for source_id, ean in res:
            get_data = """
            select id,sale_num
            from monitor.t_allegro_listing_unpublish
            where source_id=%s
            and ean=%s
            """
            data = db_java_test.fetch_all_tuple(get_data, (source_id, ean))
            one_have_sale_data = list(filter(lambda x: x[1] is not None, data))
            one_not_have_sale_data = list(filter(lambda x: x[1] is None, data))
            if len(one_have_sale_data) == 1:
                baoliu_id = one_have_sale_data[0][0]
                update_sql = f"""
                update monitor.t_allegro_listing_unpublish
                set is_baoliu = 1
                where id = {baoliu_id}
                """
                db_java_test.execute(update_sql, ())
            elif len(one_have_sale_data) > 1:
                baoliu_id = set(map(lambda x: x[0], one_have_sale_data))
                update_sql = f"""
                update monitor.t_allegro_listing_unpublish
                set is_baoliu = 1
                where id in %s
                """
                db_java_test.execute(update_sql, (baoliu_id,))
            elif len(one_have_sale_data) < 1:
                baoliu_id = one_not_have_sale_data[0][0]
                update_sql = f"""
                                update monitor.t_allegro_listing_unpublish
                                set is_baoliu = 1
                                where id = {baoliu_id}
                                """
                db_java_test.execute(update_sql, ())

    def un_publish(self):
        OfferDetailsExe = OfferDetails()
        db_java_test = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "test_jdw_database")
        get_data = f"""
                    select source_id,offer_id
            from monitor.t_allegro_listing_unpublish
            where 
             is_baoliu is NULL
        """
        data = db_java_test.fetch_all_tuple(get_data, ())
        for source_id, offer_id in data:
            try:
                res = OfferDetailsExe.put_unpublish_offer(source_id, offer_id)
                print(f"offer_id={offer_id} 下架状态码={res.status_code}")
            except Exception as e:
                print(print(f"offer_id={offer_id} 下架异常"))
                continue
            # if res.status_code == 201:
            #     print(f"offer_id={offer_id} 下架状态码={res.status_code}")
            # else:
            #     print(f"offer_id={offer_id} 下架状态码={res.status_code}")

    def cover_data(self):
        zhcxkj_center_listing_comprehensive_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"),
                                                          "zhcxkj_center_listing_comprehensive")
        zhcxkj_center_order_db = Database(ensure_path_sep("\\config\\shopeev2_config.yaml.bak"), "HWY-ZH-GZ-RDS-JavaCommon")


if __name__ == '__main__':
    GeneratePublishRepeatData().generate_company_repeat_sku_data()
